# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: http://doc.scrapy.org/en/latest/topics/item-pipeline.html
import hashlib
import pymysql
import time


class QihuSearchPipeline(object):
    def __init__(self, settings):
        self.settings = settings

    @classmethod
    def from_crawler(cls, crawler):  # crawler是使用此管道的爬虫（官方文档）
        return cls(crawler.settings)

    def process_item(self, item, spider):
        sqltext = 'insert ignore into {}(key_id,keyword,title,source_url,abstract,content,img_url,site,addtime,from_source) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'.format(
            self.settings.get('MYSQL_TABLE_KEYWORDS_NEWS'))
        self.connect.ping(reconnect=True)  # 检测断开重连
        self.cursor.execute(sqltext, (
            item['key_id'], item['keyword'], item['title'], item['source_url'], item['abstract'], item['content'],
            item['img_url'], '今日头条', int(time.time()), 2))
        return item

    def open_spider(self, spider):
        # 连接数据库
        self.connect = pymysql.connect(
            host=self.settings.get('MYSQL_HOST'),
            port=self.settings.get('MYSQL_PORT'),
            db=self.settings.get('MYSQL_DBNAME'),
            user=self.settings.get('MYSQL_USER'),
            passwd=self.settings.get('MYSQL_PASSWD'),
            charset='utf8')

        # self.connect = pymysql.connect(
        #     host='192.168.3.191',
        #     port=3306,
        #     db='hy_kw_search',
        #     user='root',
        #     passwd='abcd@1234',
        #     charset='utf8'
        # )
        # 通过cursor执行增删查改
        self.cursor = self.connect.cursor()
        self.connect.autocommit(True)

    def close_spider(self, spider):
        self.cursor.close()
        self.connect.close()
